{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT from Nobel\n",
    "\n",
    "## `nobel` Nobel Laureates\n",
    "\n",
    "We continue practicing simple SQL queries on a single table.\n",
    "\n",
    "This tutorial is concerned with a table of Nobel prize winners:\n",
    "\n",
    "```\n",
    "nobel(yr, subject, winner)\n",
    "```\n",
    "\n",
    "Using the `SELECT` statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    }
   ],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "sc = (SparkSession.builder.appName('app03') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Winners from 1950\n",
    "\n",
    "Change the query shown so that it displays Nobel prizes for 1950."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "nobel = sc.read.table('sqlzoo.nobel')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1950</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>Kurt Alder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1950</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>Otto Diels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1950</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Bertrand Russell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1950</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Philip S. Hench</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1950</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Edward C. Kendall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1950</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Tadeus Reichstein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1950</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Ralph Bunche</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1950</td>\n",
       "      <td>Physics</td>\n",
       "      <td>Cecil Powell</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr     subject             winner\n",
       "0  1950   Chemistry         Kurt Alder\n",
       "1  1950   Chemistry         Otto Diels\n",
       "2  1950  Literature   Bertrand Russell\n",
       "3  1950    Medicine    Philip S. Hench\n",
       "4  1950    Medicine  Edward C. Kendall\n",
       "5  1950    Medicine  Tadeus Reichstein\n",
       "6  1950       Peace       Ralph Bunche\n",
       "7  1950     Physics       Cecil Powell"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.filter(nobel['yr']==1950).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 1962 Literature\n",
    "\n",
    "Show who won the 1962 prize for Literature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John Steinbeck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           winner\n",
       "0  John Steinbeck"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.filter((nobel['yr']==1962) & (nobel['subject']=='Literature'))\n",
    "     .select('winner')\n",
    "     .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Albert Einstein\n",
    "\n",
    "Show the year and subject that won 'Albert Einstein' his prize."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1921</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr  subject\n",
       "0  1921  Physics"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.filter(nobel['winner']=='Albert Einstein')\n",
    "    .select('yr', 'subject').toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Recent Peace Prizes\n",
    "\n",
    "Give the name of the 'Peace' winners since the year 2000, including 2000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tunisian National Dialogue Quartet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kailash Satyarthi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Malala Yousafzai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>European Union</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ellen Johnson Sirleaf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Leymah Gbowee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tawakel Karman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Liu Xiaobo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Barack Obama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Martti Ahtisaari</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Intergovernmental Panel on Climate Change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Al Gore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Grameen Bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Muhammad Yunus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>International Atomic Energy Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Mohamed ElBaradei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Wangari Maathai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Shirin Ebadi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Jimmy Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>United Nations</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Kofi Annan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Kim Dae-jung</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       winner\n",
       "0          Tunisian National Dialogue Quartet\n",
       "1                           Kailash Satyarthi\n",
       "2                            Malala Yousafzai\n",
       "3                              European Union\n",
       "4                       Ellen Johnson Sirleaf\n",
       "5                               Leymah Gbowee\n",
       "6                              Tawakel Karman\n",
       "7                                  Liu Xiaobo\n",
       "8                                Barack Obama\n",
       "9                            Martti Ahtisaari\n",
       "10  Intergovernmental Panel on Climate Change\n",
       "11                                    Al Gore\n",
       "12                               Grameen Bank\n",
       "13                             Muhammad Yunus\n",
       "14         International Atomic Energy Agency\n",
       "15                          Mohamed ElBaradei\n",
       "16                            Wangari Maathai\n",
       "17                               Shirin Ebadi\n",
       "18                               Jimmy Carter\n",
       "19                             United Nations\n",
       "20                                 Kofi Annan\n",
       "21                               Kim Dae-jung"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.filter((nobel['yr']>=2000) & (nobel['subject']=='Peace'))\n",
    "     .select('winner')\n",
    "     .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Literature in the 1980's\n",
    "\n",
    "Show all details **(yr, subject, winner)** of the Literature prize winners for 1980 to 1989 inclusive."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1989</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Camilo José Cela</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1988</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Naguib Mahfouz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1987</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Joseph Brodsky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Wole Soyinka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1985</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Claude Simon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1984</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Jaroslav Seifert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1983</td>\n",
       "      <td>Literature</td>\n",
       "      <td>William Golding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1982</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Gabriel García Márquez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1981</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Elias Canetti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1980</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Czeslaw Milosz</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr     subject                  winner\n",
       "0  1989  Literature        Camilo José Cela\n",
       "1  1988  Literature          Naguib Mahfouz\n",
       "2  1987  Literature          Joseph Brodsky\n",
       "3  1986  Literature            Wole Soyinka\n",
       "4  1985  Literature            Claude Simon\n",
       "5  1984  Literature        Jaroslav Seifert\n",
       "6  1983  Literature         William Golding\n",
       "7  1982  Literature  Gabriel García Márquez\n",
       "8  1981  Literature           Elias Canetti\n",
       "9  1980  Literature          Czeslaw Milosz"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.filter((nobel['yr'].between(1980, 1989)) & \n",
    "             (nobel['subject']=='Literature')).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Only Presidents\n",
    "\n",
    "Show all details of the presidential winners:\n",
    "\n",
    "- Theodore Roosevelt\n",
    "- Woodrow Wilson\n",
    "- Jimmy Carter\n",
    "- Barack Obama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Barack Obama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Jimmy Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1919</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Woodrow Wilson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1906</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Theodore Roosevelt</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr subject              winner\n",
       "0  2009   Peace        Barack Obama\n",
       "1  2002   Peace        Jimmy Carter\n",
       "2  1919   Peace      Woodrow Wilson\n",
       "3  1906   Peace  Theodore Roosevelt"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.filter(nobel['winner'].isin(\n",
    "    ['Theodore Roosevelt', 'Woodrow Wilson', \n",
    "     'Jimmy Carter', 'Barack Obama'])).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. John\n",
    "\n",
    "Show the winners with first name John"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>John O'Keefe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>John B. Gurdon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>John C. Mather</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>John L. Hall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>John B. Fenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>John E. Sulston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>John Pople</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>John Hume</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>John E. Walker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>John C. Harsanyi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>John F. Nash Jr.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>John C. Polanyi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>John R. Vane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>John H. van Vleck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>John Cornforth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>John R. Hicks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>John Bardeen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>John C. Kendrew</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>John Steinbeck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>John Bardeen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>John F. Enders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>John Cockcroft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>John H. Northrop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>John R. Mott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>John Galsworthy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>John Macleod</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               winner\n",
       "0        John O'Keefe\n",
       "1      John B. Gurdon\n",
       "2      John C. Mather\n",
       "3        John L. Hall\n",
       "4        John B. Fenn\n",
       "5     John E. Sulston\n",
       "6          John Pople\n",
       "7           John Hume\n",
       "8      John E. Walker\n",
       "9    John C. Harsanyi\n",
       "10   John F. Nash Jr.\n",
       "11    John C. Polanyi\n",
       "12       John R. Vane\n",
       "13  John H. van Vleck\n",
       "14     John Cornforth\n",
       "15      John R. Hicks\n",
       "16       John Bardeen\n",
       "17    John C. Kendrew\n",
       "18     John Steinbeck\n",
       "19       John Bardeen\n",
       "20     John F. Enders\n",
       "21     John Cockcroft\n",
       "22   John H. Northrop\n",
       "23       John R. Mott\n",
       "24    John Galsworthy\n",
       "25       John Macleod"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.filter(nobel['winner'].startswith('John')).select('winner').toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Chemistry and Physics from different years\n",
    "\n",
    "**Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1984</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>Bruce Merrifield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1980</td>\n",
       "      <td>Physics</td>\n",
       "      <td>James Cronin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1980</td>\n",
       "      <td>Physics</td>\n",
       "      <td>Val Fitch</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr    subject            winner\n",
       "0  1984  Chemistry  Bruce Merrifield\n",
       "1  1980    Physics      James Cronin\n",
       "2  1980    Physics         Val Fitch"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.filter(((nobel['subject']=='Physics') & (nobel['yr']==1980)) |\n",
    "              ((nobel['subject']=='Chemistry') & (nobel['yr']==1984)))\n",
    "     .select('yr', 'subject', 'winner')\n",
    "     .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Exclude Chemists and Medics\n",
    "\n",
    "**Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1980</td>\n",
       "      <td>Economics</td>\n",
       "      <td>Lawrence R. Klein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1980</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Czeslaw Milosz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1980</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Adolfo Pérez Esquivel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1980</td>\n",
       "      <td>Physics</td>\n",
       "      <td>James Cronin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1980</td>\n",
       "      <td>Physics</td>\n",
       "      <td>Val Fitch</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr     subject                 winner\n",
       "0  1980   Economics      Lawrence R. Klein\n",
       "1  1980  Literature         Czeslaw Milosz\n",
       "2  1980       Peace  Adolfo Pérez Esquivel\n",
       "3  1980     Physics           James Cronin\n",
       "4  1980     Physics              Val Fitch"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.filter((nobel['yr']==1980) & ~ nobel['subject'].isin(['Chemistry', 'Medicine']))\n",
    "     .select('yr', 'subject', 'winner').toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Early Medicine, Late Literature\n",
    "\n",
    "Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Svetlana Alexievich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Patrick Modiano</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Alice Munro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2012</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Mo Yan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Tomas Tranströmer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2010</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Mario Vargas Llosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2009</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Herta Müller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2008</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Jean-Marie Gustave Le Clézio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2007</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Doris Lessing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2006</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Orhan Pamuk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2005</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Harold Pinter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2004</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Elfriede Jelinek</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1909</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Theodor Kocher</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1908</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Paul Ehrlich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1908</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Ilya Mechnikov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1907</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Alphonse Laveran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1906</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Camillo Golgi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1906</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Santiago Ramón y Cajal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1905</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Robert Koch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1904</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Ivan Pavlov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1903</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Niels Ryberg Finsen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1902</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Ronald Ross</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1901</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Emil von Behring</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      yr     subject                        winner\n",
       "0   2015  Literature           Svetlana Alexievich\n",
       "1   2014  Literature               Patrick Modiano\n",
       "2   2013  Literature                   Alice Munro\n",
       "3   2012  Literature                        Mo Yan\n",
       "4   2011  Literature             Tomas Tranströmer\n",
       "5   2010  Literature            Mario Vargas Llosa\n",
       "6   2009  Literature                  Herta Müller\n",
       "7   2008  Literature  Jean-Marie Gustave Le Clézio\n",
       "8   2007  Literature                 Doris Lessing\n",
       "9   2006  Literature                   Orhan Pamuk\n",
       "10  2005  Literature                 Harold Pinter\n",
       "11  2004  Literature              Elfriede Jelinek\n",
       "12  1909    Medicine                Theodor Kocher\n",
       "13  1908    Medicine                  Paul Ehrlich\n",
       "14  1908    Medicine                Ilya Mechnikov\n",
       "15  1907    Medicine              Alphonse Laveran\n",
       "16  1906    Medicine                 Camillo Golgi\n",
       "17  1906    Medicine        Santiago Ramón y Cajal\n",
       "18  1905    Medicine                   Robert Koch\n",
       "19  1904    Medicine                   Ivan Pavlov\n",
       "20  1903    Medicine           Niels Ryberg Finsen\n",
       "21  1902    Medicine                   Ronald Ross\n",
       "22  1901    Medicine              Emil von Behring"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.filter(((nobel['yr']<1910) & (nobel['subject']=='Medicine')) |\n",
    "              ((nobel['yr']>=2004) & (nobel['subject']=='Literature')))\n",
    "         .select('yr', 'subject', 'winner')\n",
    "         .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Umlaut\n",
    "\n",
    "Find all details of the prize won by PETER GRÜNBERG\n",
    "\n",
    "> _Non-ASCII characters_   \n",
    "> The u in his name has an umlaut. You may find this link useful <https://en.wikipedia.org/wiki/%C3%9C#Keyboarding>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2007</td>\n",
       "      <td>Physics</td>\n",
       "      <td>Peter Grünberg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr  subject          winner\n",
       "0  2007  Physics  Peter Grünberg"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql.functions import upper\n",
    "nobel.filter(upper(nobel['winner'])=='PETER GRÜNBERG').toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Apostrophe\n",
    "\n",
    "Find all details of the prize won by EUGENE O'NEILL\n",
    "\n",
    "> _Escaping single quotes_   \n",
    "> You can't put a single quote in a quote string directly. You can use two single quotes within a quoted string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1936</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Eugene O'Neill</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     yr     subject          winner\n",
       "0  1936  Literature  Eugene O'Neill"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.filter(upper(nobel['winner'])=='EUGENE O\\'NEILL').toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. Knights of the realm\n",
    "\n",
    "Knights in order\n",
    "\n",
    "**List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Sir Martin J. Evans</td>\n",
       "      <td>2007</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sir Peter Mansfield</td>\n",
       "      <td>2003</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sir Paul Nurse</td>\n",
       "      <td>2001</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sir Harold Kroto</td>\n",
       "      <td>1996</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sir James W. Black</td>\n",
       "      <td>1988</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Sir Arthur Lewis</td>\n",
       "      <td>1979</td>\n",
       "      <td>Economics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Sir Nevill F. Mott</td>\n",
       "      <td>1977</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Sir Bernard Katz</td>\n",
       "      <td>1970</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Sir John Eccles</td>\n",
       "      <td>1963</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Sir Frank Macfarlane Burnet</td>\n",
       "      <td>1960</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Sir Cyril Hinshelwood</td>\n",
       "      <td>1956</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sir Robert Robinson</td>\n",
       "      <td>1947</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Sir Alexander Fleming</td>\n",
       "      <td>1945</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Sir Howard Florey</td>\n",
       "      <td>1945</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Sir Henry Dale</td>\n",
       "      <td>1936</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Sir Norman Angell</td>\n",
       "      <td>1933</td>\n",
       "      <td>Peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Sir Charles Sherrington</td>\n",
       "      <td>1932</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Sir Venkata Raman</td>\n",
       "      <td>1930</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Sir Frederick Hopkins</td>\n",
       "      <td>1929</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Sir Austen Chamberlain</td>\n",
       "      <td>1925</td>\n",
       "      <td>Peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Sir William Ramsay</td>\n",
       "      <td>1904</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         winner    yr    subject\n",
       "0           Sir Martin J. Evans  2007   Medicine\n",
       "1           Sir Peter Mansfield  2003   Medicine\n",
       "2                Sir Paul Nurse  2001   Medicine\n",
       "3              Sir Harold Kroto  1996  Chemistry\n",
       "4            Sir James W. Black  1988   Medicine\n",
       "5              Sir Arthur Lewis  1979  Economics\n",
       "6            Sir Nevill F. Mott  1977    Physics\n",
       "7              Sir Bernard Katz  1970   Medicine\n",
       "8               Sir John Eccles  1963   Medicine\n",
       "9   Sir Frank Macfarlane Burnet  1960   Medicine\n",
       "10        Sir Cyril Hinshelwood  1956  Chemistry\n",
       "11          Sir Robert Robinson  1947  Chemistry\n",
       "12        Sir Alexander Fleming  1945   Medicine\n",
       "13            Sir Howard Florey  1945   Medicine\n",
       "14               Sir Henry Dale  1936   Medicine\n",
       "15            Sir Norman Angell  1933      Peace\n",
       "16      Sir Charles Sherrington  1932   Medicine\n",
       "17            Sir Venkata Raman  1930    Physics\n",
       "18        Sir Frederick Hopkins  1929   Medicine\n",
       "19       Sir Austen Chamberlain  1925      Peace\n",
       "20           Sir William Ramsay  1904  Chemistry"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql.functions import desc\n",
    "(nobel.filter(nobel['winner'].startswith('Sir'))\n",
    "     .select('winner', 'yr', 'subject')\n",
    "     .orderBy(desc('yr'), 'winner').toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14. Chemistry and Physics last\n",
    "\n",
    "The expression **subject IN ('Chemistry','Physics')** can be used as a value - it will be 0 or 1.\n",
    "\n",
    "**Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "      <th>subject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Richard Stone</td>\n",
       "      <td>Economics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jaroslav Seifert</td>\n",
       "      <td>Literature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>César Milstein</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Georges J.F. Köhler</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Niels K. Jerne</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Desmond Tutu</td>\n",
       "      <td>Peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Bruce Merrifield</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Carlo Rubbia</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Simon van der Meer</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                winner     subject\n",
       "0        Richard Stone   Economics\n",
       "1     Jaroslav Seifert  Literature\n",
       "2       César Milstein    Medicine\n",
       "3  Georges J.F. Köhler    Medicine\n",
       "4       Niels K. Jerne    Medicine\n",
       "5         Desmond Tutu       Peace\n",
       "6     Bruce Merrifield   Chemistry\n",
       "7         Carlo Rubbia     Physics\n",
       "8   Simon van der Meer     Physics"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.withColumn('flg', nobel['subject'].isin(['Chemistry', 'Physics']))\n",
    "      .filter(nobel['yr']==1984)\n",
    "      .orderBy('flg', 'subject', 'winner')\n",
    "      .select('winner', 'subject')\n",
    "      .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
